"""
pandas + openpyxl 协同使用具体示例
结合两者的优势：pandas处理数据 + openpyxl美化输出
"""

import pandas as pd
import openpyxl
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.chart import BarChart, Reference
import numpy as np

# ==================== 1. 数据准备（pandas处理） ====================

def prepare_data_with_pandas():
    """使用pandas准备和清洗数据"""
    
    # 创建示例数据
    np.random.seed(42)
    data = {
        '产品名称': [f'产品{i}' for i in range(1, 6)],
        '1月销量': np.random.randint(100, 500, 5),
        '2月销量': np.random.randint(100, 500, 5),
        '3月销量': np.random.randint(100, 500, 5),
        '单价': [100, 150, 200, 120, 180],
        '成本': [60, 90, 120, 70, 100]
    }
    
    df = pd.DataFrame(data)
    
    # pandas数据处理
    df['总销售额'] = df['1月销量'] * df['单价'] + df['2月销量'] * df['单价'] + df['3月销量'] * df['单价']
    df['总成本'] = df['1月销量'] * df['成本'] + df['2月销量'] * df['成本'] + df['3月销量'] * df['成本']
    df['总利润'] = df['总销售额'] - df['总成本']
    df['利润率'] = (df['总利润'] / df['总销售额'] * 100).round(2)
    
    # 添加统计行
    summary = pd.DataFrame({
        '产品名称': ['总计'],
        '1月销量': [df['1月销量'].sum()],
        '2月销量': [df['2月销量'].sum()],
        '3月销量': [df['3月销量'].sum()],
        '单价': [''],
        '成本': [''],
        '总销售额': [df['总销售额'].sum()],
        '总成本': [df['总成本'].sum()],
        '总利润': [df['总利润'].sum()],
        '利润率': [(df['总利润'].sum() / df['总销售额'].sum() * 100).round(2)]
    })
    
    df = pd.concat([df, summary], ignore_index=True)
    
    print("✅ pandas数据处理完成")
    print(df)
    
    return df

# ==================== 2. 使用openpyxl美化输出 ====================

def beautify_excel_with_openpyxl(df, output_filename):
    """使用openpyxl美化Excel输出"""
    
    # 先用pandas写入基础数据
    with pd.ExcelWriter(output_filename, engine='openpyxl') as writer:
        df.to_excel(writer, sheet_name='销售报表', index=False)
    
    # 再用openpyxl打开进行美化
    wb = openpyxl.load_workbook(output_filename)
    ws = wb['销售报表']
    
    # 定义样式
    header_font = Font(name='微软雅黑', size=12, bold=True, color="FFFFFF")
    header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
    header_alignment = Alignment(horizontal="center", vertical="center")
    
    data_font = Font(name='宋体', size=10)
    currency_font = Font(name='宋体', size=10, bold=True)
    summary_font = Font(name='宋体', size=10, bold=True, color="FF0000")
    
    thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), 
                        top=Side(style='thin'), bottom=Side(style='thin'))
    
    # 美化表头
    for cell in ws[1]:
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = header_alignment
    
    # 美化数据行
    for row in ws.iter_rows(min_row=2, max_row=ws.max_row-1, 
                           min_col=1, max_col=ws.max_column):
        for cell in row:
            cell.font = data_font
            cell.border = thin_border
    
    # 美化统计行（最后一行）
    for cell in ws[ws.max_row]:
        cell.font = summary_font
        cell.fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
        cell.border = thin_border
    
    # 设置数字格式
    currency_columns = ['单价', '成本', '总销售额', '总成本', '总利润']
    for col in range(1, ws.max_column + 1):
        col_letter = openpyxl.utils.get_column_letter(col)
        header_value = ws.cell(row=1, column=col).value
        
        if header_value in currency_columns:
            for row in range(2, ws.max_row + 1):
                cell = ws.cell(row=row, column=col)
                if isinstance(cell.value, (int, float)) and cell.value != '':
                    cell.number_format = '#,##0.00'
                    cell.font = currency_font
        
        if header_value == '利润率':
            for row in range(2, ws.max_row + 1):
                cell = ws.cell(row=row, column=col)
                if isinstance(cell.value, (int, float)) and cell.value != '':
                    cell.number_format = '0.00%'
    
    # 调整列宽
    for col in ws.columns:
        max_length = 0
        column_letter = openpyxl.utils.get_column_letter(col[0].column)
        
        for cell in col:
            try:
                if len(str(cell.value)) > max_length:
                    max_length = len(str(cell.value))
            except:
                pass
        
        adjusted_width = (max_length + 2) * 1.2
        ws.column_dimensions[column_letter].width = adjusted_width
    
    # 添加图表
    chart = BarChart()
    chart.title = "各产品季度销量对比"
    chart.x_axis.title = "产品名称"
    chart.y_axis.title = "销量"
    
    # 设置数据范围（排除总计行）
    data = Reference(ws, min_col=2, min_row=1, max_row=ws.max_row-1, max_col=4)
    categories = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row-1)
    
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(categories)
    
    # 将图表添加到工作表
    ws.add_chart(chart, "K2")
    
    # 保存美化后的文件
    wb.save(output_filename)
    print(f"✅ openpyxl美化完成，文件保存为: {output_filename}")
    
    return wb

# ==================== 3. 高级功能：多工作表协同 ====================

def multi_sheet_collaboration():
    """多工作表协同操作"""
    
    # 创建多个数据表
    sales_data = {
        '区域': ['华东', '华南', '华北', '西南', '西北'],
        'Q1销售额': [500000, 450000, 480000, 320000, 280000],
        'Q2销售额': [520000, 470000, 490000, 330000, 290000],
        'Q3销售额': [550000, 480000, 510000, 340000, 300000]
    }
    
    product_data = {
        '产品线': ['A系列', 'B系列', 'C系列', 'D系列'],
        '市场份额': [0.35, 0.25, 0.20, 0.20],
        '增长率': [0.15, 0.12, 0.08, 0.05]
    }
    
    df_sales = pd.DataFrame(sales_data)
    df_product = pd.DataFrame(product_data)
    
    # 先用pandas写入多个工作表
    with pd.ExcelWriter('多工作表报表.xlsx', engine='openpyxl') as writer:
        df_sales.to_excel(writer, sheet_name='区域销售', index=False)
        df_product.to_excel(writer, sheet_name='产品分析', index=False)
    
    # 再用openpyxl进行统一美化
    wb = openpyxl.load_workbook('多工作表报表.xlsx')
    
    # 为所有工作表应用统一样式
    for sheet_name in wb.sheetnames:
        ws = wb[sheet_name]
        
        # 应用基本样式
        for cell in ws[1]:
            cell.font = Font(name='微软雅黑', size=12, bold=True, color="FFFFFF")
            cell.fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
        
        # 调整列宽
        for col in ws.columns:
            max_length = 0
            for cell in col:
                try:
                    if len(str(cell.value)) > max_length:
                        max_length = len(str(cell.value))
                except:
                    pass
            adjusted_width = (max_length + 2) * 1.2
            ws.column_dimensions[openpyxl.utils.get_column_letter(col[0].column)].width = adjusted_width
    
    wb.save('多工作表报表.xlsx')
    print("✅ 多工作表协同处理完成")
    
    return wb

# ==================== 4. 实用函数：数据验证和条件格式 ====================

def advanced_features():
    """高级功能：数据验证和条件格式"""
    
    # 创建示例数据
    df = pd.DataFrame({
        '员工姓名': ['张三', '李四', '王五', '赵六'],
        '部门': ['销售部', '技术部', '人事部', '财务部'],
        '绩效评分': [85, 92, 78, 95],
        '奖金': [5000, 6000, 4500, 7000]
    })
    
    # pandas写入
    with pd.ExcelWriter('高级功能示例.xlsx', engine='openpyxl') as writer:
        df.to_excel(writer, sheet_name='员工绩效', index=False)
    
    # openpyxl添加高级功能
    wb = openpyxl.load_workbook('高级功能示例.xlsx')
    ws = wb['员工绩效']
    
    # 条件格式：高亮高分员工
    from openpyxl.formatting.rule import CellIsRule
    
    # 绩效评分大于90的标绿色
    green_fill = PatternFill(start_color="90EE90", end_color="90EE90", fill_type="solid")
    high_perf_rule = CellIsRule(operator='greaterThan', formula=['90'], fill=green_fill)
    ws.conditional_formatting.add(f'C2:C{ws.max_row}', high_perf_rule)
    
    # 奖金大于6000的标橙色
    orange_fill = PatternFill(start_color="FFA500", end_color="FFA500", fill_type="solid")
    high_bonus_rule = CellIsRule(operator='greaterThan', formula=['6000'], fill=orange_fill)
    ws.conditional_formatting.add(f'D2:D{ws.max_row}', high_bonus_rule)
    
    wb.save('高级功能示例.xlsx')
    print("✅ 高级功能添加完成")
    
    return wb

# ==================== 主程序 ====================

if __name__ == "__main__":
    print("🚀 pandas + openpyxl 协同使用示例")
    print("=" * 60)
    
    # 1. 基础协同示例
    print("\n1. 基础协同：pandas处理数据 + openpyxl美化")
    df = prepare_data_with_pandas()
    wb = beautify_excel_with_openpyxl(df, '协同报表.xlsx')
    
    # 2. 多工作表协同
    print("\n2. 多工作表协同处理")
    multi_sheet_collaboration()
    
    # 3. 高级功能
    print("\n3. 高级功能：条件格式")
    advanced_features()
    
    print("\n" + "=" * 60)
    print("🎉 协同使用示例完成！")
    print("\n📁 生成的文件：")
    print("  - 协同报表.xlsx")
    print("  - 多工作表报表.xlsx") 
    print("  - 高级功能示例.xlsx")
    
    print("\n💡 核心优势：")
    print("  ✅ pandas：快速数据处理、统计分析")
    print("  ✅ openpyxl：精细格式控制、图表美化")
    print("  ✅ 协同：发挥各自优势，实现高效报表生成")